package com.expense.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.expense.util.PaginatedList;
import com.expense.util.PropertiesReader;
import com.expense.util.SqlConfig;

 

/**
 * JDBC数据库连接 数据库操作	@author xj
 */
public class Dao {
	private Logger logger = Logger.getLogger(this.getClass());
	private Connection conn=null;
	private ThreadLocal<Connection> conn2 = new ThreadLocal<Connection>();
	private Statement st=null;
	private PreparedStatement ps = null;
	public Connection getConn() {
		return this.conn;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public Dao(){
		this.getCon();
	}
	
	
	
	 
	
	/**
	 * 打开链接
	 * */
	public  void getCon(){
	 
		try {
			Class.forName(SqlConfig.MSQL_DB_DRIVER).newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try {
				conn = DriverManager.getConnection(SqlConfig.MSQL_DB_URL,SqlConfig.MSQL_DB_USER, SqlConfig.MSQL_DB_PASSWORD);
				if(conn!=null)
					st = conn.createStatement();
				 
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("数据库连接失败!");
		}
		
		 
	}
	
	 
	
	
	
	/**
	 * 关闭链接
	 * */
	public  void CloseCon(){
		try {
			    if(ps!=null)
				    ps.close();
			
				if(st!=null)
					st.close();
				
				if(conn!=null)
					conn.close();
				
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 执行语句
	 * */
	public  boolean executeSql(String sql) {
		boolean iflag=false;
		if(conn!=null&&st!=null) {
			try {
					st.execute(sql);
					iflag=true;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else {
			logger.error("没有打开数据库连接!");
		}
		return iflag;
	}
	
	
	/**
	 * 执行新增语句 返回新增主键
	 * */
	public  int executeInsertSql(String sql) {
		boolean iflag=true;
		ResultSet rs = null;
        int id = 0;
        if(conn!=null&&st!=null) {
			try {
				ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
	            ps.executeUpdate();
	            rs = ps.getGeneratedKeys();
	            if (rs!=null&&rs.next()){
	                id = rs.getInt(1);
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }else {
			logger.error("没有打开数据库连接!");
		}
		return id;
	}
	
	
	/**
	 * 执行语句，返回集合
	 * */
	public  ResultSet selectData(String sql){
		ResultSet rs=null;
		if(conn!=null&&st!=null) {
			try { 
				rs=st.executeQuery(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			logger.error("没有打开数据库连接!");
		}
		return  rs;
		
	}
	
	
	
	/**
	 * 分页，返回集合

	 * */
	public  ResultSet pageResultSetData(int pageSize ,int currentPage, String tableName,String primaryColumnName,String whereSql,String orderSql){
		String sql="";
		if (SqlConfig.MSQL_DB_Type.equals(SqlConfig.SQL_SERVER)) {
			sql=this.pageSqlSqlServer(pageSize, currentPage, tableName, primaryColumnName,whereSql, orderSql);
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.ORACLE)) {
			sql=this.pageSqlOracle(pageSize, currentPage, tableName, whereSql, orderSql);
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.MYSQL)) {
			sql=this.pageSqlMySql(pageSize, currentPage, tableName, whereSql, orderSql);
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.ACCESS)) {
			sql=this.pageSqlAccess(pageSize, currentPage, tableName,primaryColumnName, whereSql, orderSql);
		}
		logger.info("pageSql:"+sql);
		
		ResultSet rs=this.selectData(sql);
		return  rs;	
	}
	
	
	/**
	 * 分页，返回集合

	 * */
	public  PaginatedList paginatedListData(int pageSize ,int currentPage, String tableName,String whereSql,String orderSql){
		int countSize=this.getCountSize(tableName, whereSql);
		int countPage=1;//总页数
		if(countSize!=0){
            countPage=countSize/pageSize;
            if(countSize%pageSize!=0){
                countPage++;
            }
        }
		
		int start=(currentPage-1)*pageSize+1;
        int end=currentPage*pageSize;
        if(start<0){
            start=0;
        }

		PaginatedList paginatedList=new PaginatedList();
		paginatedList.setCurrentPage(currentPage);
		paginatedList.setEndIndex(end);
		paginatedList.setNextIndex(end+1);
		paginatedList.setPages(countPage);
		paginatedList.setPageSize(pageSize);
		paginatedList.setStartIndex(start);
		paginatedList.setTotleCount(countSize);
		
		 
		
		return  paginatedList;	
	}
	
	
	/**
	 * 得到分页总记录数
	 * */
	public int getCountSize(String tableName,String whereSql) {
		int countSize=0;
		String sql="select  count(*)  from  "+ tableName+" ";
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" "+whereSql;
        }
        
        logger.info("countSql:"+sql);
        ResultSet rs=this.selectData(sql);
        try {
			if(rs!=null&&rs.next()){
			    countSize=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
        return countSize;
	}
	
	
	/**
	 * 转成sqlserver 分页语句
	 * **/
	public String pageSqlSqlServer(int pageSize ,int currentPage, String tableName,String primaryColumnName,String whereSql,String orderSql) {
		/*SELECT TOP 页大小 *
		FROM table1
		WHERE id NOT IN
		          (
		          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
		          )
		ORDER BY id*/
		
		
		//int start=(currentPage-1)*pageSize+1;
        //int end=currentPage*pageSize;
        //if(start<0){
        //    start=0;
       // }
        
       String sql="select top "+pageSize+" * "+ " from  "+ tableName+" ";
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" "+whereSql;
        }
        
        
        String aldSql="select top "+pageSize*(currentPage-1)+" "+primaryColumnName+"  "+"    from  "+tableName+" ";
         if(whereSql!=null&&!whereSql.equals("")){
                aldSql+=" "+whereSql;
         }
        if(orderSql!=null&&!orderSql.equals("")){
            aldSql+=" "+orderSql;
        } 
        
        
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" and ";
        } else{
            sql+=" where ";
        }
        
        sql+="  "+primaryColumnName+" NOT IN ("+aldSql+") ";
      
       
        if(orderSql!=null&&!orderSql.equals("")){
            sql+=" "+orderSql;
        }  
        
       
		return sql;
	}
	
	/**
	 * 转成sqlserver 分页语句
	 * **/
	public String pageSqlSqlServer_bak(int pageSize ,int currentPage, String tableName,String primaryColumnName,String whereSql,String orderSql) {
        int start=(currentPage-1)*pageSize+1;
        int end=currentPage*pageSize;
        if(start<0){
            start=0;
        }
        
       String sql="select top "+pageSize+" * "+ " from  "+ tableName+" ";
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" "+whereSql;
        }
        
        
        String aldSql="select top "+start+" "+primaryColumnName+"  "+"    from  "+tableName+" ";
         if(whereSql!=null&&!whereSql.equals("")){
                aldSql+=" "+whereSql;
         }
        if(orderSql!=null&&!orderSql.equals("")){
            aldSql+=" "+orderSql;
        } 
        
        
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" and ";
        } else{
            sql+=" where ";
        }
        sql+="  ("+primaryColumnName+" >= (select max("+primaryColumnName+")  from ("+aldSql+") as t)) ";
      
       
        if(orderSql!=null&&!orderSql.equals("")){
            sql+=" "+orderSql;
        }  
        
       
		return sql;
	}
	
	
	/**
	 * 转成oracle 分页语句
	 * **/
	public String pageSqlOracle(int pageSize ,int currentPage, String tableName,String whereSql,String orderSql) {
		int start=(currentPage-1)*pageSize+1;
        int end=currentPage*pageSize;
        if(start<0){
            start=0;
        }
        String sql=" select * from "+tableName;
	        if(whereSql!=null&&!whereSql.equals("")){
             sql+=" "+whereSql;
         }
	        if(orderSql!=null&&!orderSql.equals("")){
             sql+=" "+orderSql;
         } 
         
         sql="select * from (select a.*, rownum rn from ("+sql+") a ) where rn >= "+start+ " and   rownum < "+end ;
         return sql;
	}
	
	/**
	 * 转成mySql 分页语句
	 * */
	public String pageSqlMySql(int pageSize ,int currentPage, String tableName,String whereSql,String orderSql) {
		int start=(currentPage-1)*pageSize+1;
        int end=currentPage*pageSize;
        if(start<0){
            start=0;
        }
        String  sql=" select * from "+tableName+" limit "+start+","+end+" ";
	       if(whereSql!=null&&!whereSql.equals("")){
             sql+=" "+whereSql;
         }
         if(orderSql!=null&&!orderSql.equals("")){
             sql+=" "+orderSql;
         } 
         
         return sql;
	}
	
	/**
	 * 转成access 分页语句
	 * */
	public String pageSqlAccess(int pageSize ,int currentPage, String tableName,String primaryColumnName,String whereSql,String orderSql) {
		int start=(currentPage-1)*pageSize+1;
        int end=currentPage*pageSize;
        if(start<0){
            start=0;
        }
        
       String sql="select top "+pageSize+" * "+ " from  "+ tableName+" ";
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" "+whereSql;
        }

        String aldSql="select top "+start+" "+primaryColumnName+"  "+"    from  "+tableName+" ";
         if(whereSql!=null&&!whereSql.equals("")){
                aldSql+=" "+whereSql;
         }
        if(orderSql!=null&&!orderSql.equals("")){
            aldSql+=" "+orderSql;
        } 
        
        
        if(whereSql!=null&&!whereSql.equals("")){
            sql+=" and ";
        } else{
            sql+=" where ";
        }
        sql+="  ("+primaryColumnName+" >= (select max("+primaryColumnName+")  from ("+aldSql+") as t)) ";
      
       
        if(orderSql!=null&&!orderSql.equals("")){
            sql+=" "+orderSql;
        }  
		return sql;
	}
	
	
	
	
}
